Types of Commands in SQL Server

CREATE DATABASE DB2  
GO  
CREATE TABLE tblDemo  
(  
   Id int primary key,  
   Name char(20)  
)  
GO  
DROP DATABASE DB2  

INSERT INTO tblDemo VALUES (1,'Abhishek')  
GO  
DELETE FROM tblDemo WHERE Id = 4  
GO  
UPDATE tblDemo  
SET Name = 'Sunny'  
WHERE Id = 6  
GO  

Data Control Language (DCL) commands are for security purposes. These commands provide roles, permissions, access, and so on.

The following are the commands in this category. Find detailed articles about DCL Commands in SQL here: DCL Commands in SQL

Command	Description
Grant	Provide user access to the database or any other object.
Revoke	Take back the access from the user.

For example, we have the following data.

Database: CSharpCornerDB

Table

User: CSharpCorner

Currently, we haven't provided any permission for this user.

Now, we'll create a table in the CSharpCornerDB database.

CREATE table tblArticles  
(  
    ArticleId int primary key identity,  
    ArticleName varchar(10),  
    Category varchar(10)  
)  

If we execute this command, we'll get an error message.

Msg 262, Level 14, State 1, Line 1

CREATE TABLE permission denied in database 'CSharpCornerDB.'

This is because this user is not permitted to create anything right now. In our next article, we'll learn how to grant or revoke permission on objects.

TCL Commands in SQL

Transaction Control Language (TCL) commands you to manage SQL server transactions. The following are the commands in this category.

Command	Description
Commit	Used to save any transaction permanently.
Rollback	This command is used to restore the database to its last committed state.
Save Tran	This command is used to save the transaction so that we can roll back that transaction to the point whenever necessary.

For example, we have a table named "tblStudent" with three records, as shown below.

Now, we'll begin our transaction, add another record, and commit that transaction.

Begin Tran  
Insert INTO tblStudents VALUES ('Sumit')  
COMMIT 

Now, we have four records.

Now, we'll add three records, one by one, with save point, but we don't commit our transaction.

Begin Tran  
Insert INTO tblStudents VALUES ('Kajal')  
SAVE Tran A;  
Insert INTO tblStudents VALUES ('Rahul')  
SAVE Tran B;  
Insert INTO tblStudents VALUES ('Ram')  
SAVE Tran C;  
  
SELECT * from tblStudents  

We now have the following records in the table, in which the last three records are not yet committed.

Now, we have three savepoints - A, B, and C. Since our transaction is not yet committed, we can roll it back to any savepoint. We'll roll it back to point B, i.e., at "Rahul."

ROLLBACK TRAN B  
COMMIT  

When you fire the Select query, you'll get records up to ID 6.

So, these were the categories and types of commands in SQL Server with which you can play with the data.

Conclusion

In this article, we have seen the types of commands in SQL Server and got an overview. We also saw how to commit transactions and roll back any transaction to any savepoint. In my next article, we'll explain how to deal with the GRANT and REVOKE commands. Until then, keep learning and keep sharing.

If there's any mistake in this article, please let me know. Also, please give your valuable feedback and comments that enable me to provide a better article next time.